﻿using System;
using System.Collections.Generic;

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Profile;
using System.Data;
using System.Text;
using Gift.Model.ENUM;

using Gift.DAL;
using Gift.Model;

namespace WebSite.admin.Report
{
    public partial class Report : BaseAdmin
    {

    

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.GridView1.PageIndex = this.ddlCurrentPage.SelectedIndex;
            this.GridViewBind("");
        }


        private string GetSql()
        {

            if (this.ddlProfessionAreaType.SelectedItem.Value != "-1")
            {
                string tempSql = "  select distinct CT.Name,C.cardprice from DB_Card C join tbCardType CT on C.CardTypeId = CT.Id "
                                 + " and CT.ProfessionAreaTypeId = " + this.ddlProfessionAreaType.SelectedItem.Value;


                StringBuilder sbSql = new StringBuilder();
                DataTable dtTemp = new DataTable();
                dtTemp = SQLAccess.GreatDs(tempSql).Tables[0];
                string sql = "";
                if (dtTemp.Rows.Count <= 0)
                {
                    sql = "select top 0 '1900-01-01' as 日期,1 as 订单数量 ,"
                         + " 0 as 专区使用卡片数量, "
                         + " 0 as 用户充值卡片数量 "
                         + " from DB_Order ";
                    return sql;
                }
                foreach (DataRow dr in dtTemp.Rows)
                {
                    sbSql.Append("  (select count(1) from tbOrderAccount OA join DB_Card C on OA.Cardid=C.Cardid  Join tbCardType CT on C.CardTypeId = CT.Id "
                             + "  where  convert(varchar({0}),AddDate,121) = convert(varchar({0}),DB_Order.ordertime,121) "
                                     + " and C.CardPrice=" + dr["cardprice"].ToString() + " and CT.Name = '" + dr["Name"].ToString() + "' and OA.CardId is not null and OA.CardAdded=1 ) "
                             + " +   (select count(1) from tbMemberAccount MA join DB_Card C on MA.Cardid=C.Cardid  Join tbCardType CT on C.CardTypeId = CT.Id "
                             + "  where  convert(varchar({0}),AddDate,121) = convert(varchar({0}),DB_Order.ordertime,121) "
                                     + " and C.CardPrice=" + dr["cardprice"].ToString() + " and CT.Name = '" + dr["Name"].ToString() + "' and MA.CardId is not null  ) "
                                    + " as " + dr["Name"].ToString() + "型" + dr["CardPrice"].ToString() + ",");
                }

                sql = "select convert(varchar({0}),ordertime,121) as 日期,count(1) as 订单数量 ,"
                          + " (select count(distinct cardid) from tbOrderAccount where  convert(varchar({0}),AddDate,121) = convert(varchar({0}),DB_Order.ordertime,121) and CardId is not null and CardAdded=1 "
                          + "  and CardId in (select C.CardId from DB_Card C join tbCardType CT on C.CardTypeId = CT.Id and CT.ProfessionAreaTypeId = " + this.ddlProfessionAreaType.SelectedItem.Value
                          + ")"
                          + " ) as 专区使用卡片数量, "
                          + sbSql.ToString()
                          + " (select count(distinct cardid) from tbMemberAccount where  convert(varchar({0}),AddDate,121) = convert(varchar({0}),DB_Order.ordertime,121) and CardId is not null "
                           + "  and CardId in (select C.CardId from DB_Card C join tbCardType CT on C.CardTypeId = CT.Id and CT.ProfessionAreaTypeId = " + this.ddlProfessionAreaType.SelectedItem.Value
                             + ")"
                          + " ) as 用户充值卡片数量 "
                          + " from DB_Order "
                          + " group by convert(varchar({0}),ordertime,121) ";

                if (!string.IsNullOrEmpty(this.ddlMonth.SelectedItem.Value))
                {
                    string sMonth = "";
                    if (int.Parse(this.ddlMonth.SelectedItem.Value) < 10)
                    {
                        sMonth = "0" + this.ddlMonth.SelectedItem.Value;
                    }
                    sql = sql + " having substring(convert(varchar({0}),ordertime,121),1,7) ='" + this.ddlYear.SelectedItem.Value + "-" + sMonth + "'";
                }
                else
                {
                    sql = sql + " having substring(convert(varchar({0}),ordertime,121),1,4) ='" + this.ddlYear.SelectedItem.Value + "'";
                }

                if (this.DropDownList1.SelectedItem.Value == "1")
                {

                    sql = string.Format(sql, "10");

                }
                else
                {
                    sql = string.Format(sql, "7");
                }
                return sql;
            }
            else
            {

                string tempSql = "  select Id,Name from tbProfessionAreaType where isnull(IsEnable,0)=1 and Id != -1";

                StringBuilder sbSql = new StringBuilder();
                DataTable dtTemp = new DataTable();
                dtTemp = SQLAccess.GreatDs(tempSql).Tables[0];
                string sql = "";
                if (dtTemp.Rows.Count <= 0)
                {
                    sql = "select top 0 '1900-01-01' as 日期,1 as 订单数量 ,"
                         + " 0 as 专区使用卡片数量, "
                         + " 0 as 用户充值卡片数量 "
                         + " from DB_Order ";
                    return sql;
                }
                foreach (DataRow dr in dtTemp.Rows)
                {
                    sbSql.Append("  (select count(1) from tbOrderAccount OA join DB_Card C on OA.Cardid=C.Cardid  Join tbCardType CT on C.CardTypeId = CT.Id "
                               + "  where  convert(varchar({0}),AddDate,121) = convert(varchar({0}),DB_Order.ordertime,121) "
                               + " and CT.ProfessionAreaTypeId=" + dr["Id"].ToString()  + " and OA.CardId is not null and OA.CardAdded=1 ) "
                               + " +   (select count(1) from tbMemberAccount MA join DB_Card C on MA.Cardid=C.Cardid  Join tbCardType CT on C.CardTypeId = CT.Id "
                               + "  where  convert(varchar({0}),AddDate,121) = convert(varchar({0}),DB_Order.ordertime,121) "
                                     + " and CT.ProfessionAreaTypeId=" + dr["Id"].ToString() +  " and MA.CardId is not null  ) "
                                    + " as " + dr["Name"].ToString() + ",");
                }

                sql = "select convert(varchar({0}),ordertime,121) as 日期,count(1) as 订单数量 ,"
                          + " (select count(distinct cardid) from tbOrderAccount where  convert(varchar({0}),AddDate,121) = convert(varchar({0}),DB_Order.ordertime,121) and CardId is not null and CardAdded=1 "
                          + "  and CardId in (select C.CardId from DB_Card C join tbCardType CT on C.CardTypeId = CT.Id "
                          + ")"
                          + " ) as 专区使用卡片数量, "
                          + sbSql.ToString()
                          + " (select count(distinct cardid) from tbMemberAccount where  convert(varchar({0}),AddDate,121) = convert(varchar({0}),DB_Order.ordertime,121) and CardId is not null "
                           + "  and CardId in (select C.CardId from DB_Card C join tbCardType CT on C.CardTypeId = CT.Id "
                             + ")"
                          + " ) as 用户充值卡片数量 "
                          + " from DB_Order "
                          + " group by convert(varchar({0}),ordertime,121) ";

                if (!string.IsNullOrEmpty(this.ddlMonth.SelectedItem.Value))
                {
                    string sMonth = "";
                    if (int.Parse(this.ddlMonth.SelectedItem.Value) < 10)
                    {
                        sMonth = "0" + this.ddlMonth.SelectedItem.Value;
                    }
                    sql = sql + " having substring(convert(varchar({0}),ordertime,121),1,7) ='" + this.ddlYear.SelectedItem.Value + "-" + sMonth + "'";
                }
                else
                {
                    sql = sql + " having substring(convert(varchar({0}),ordertime,121),1,4) ='" + this.ddlYear.SelectedItem.Value + "'";
                }

                if (this.DropDownList1.SelectedItem.Value == "1")
                {

                    sql = string.Format(sql, "10");

                }
                else
                {
                    sql = string.Format(sql, "7");
                }
                return sql;
            }
          
        }

        private void BindProfessionAreaType()
        {
            DataSet dsType = new DataSet();
            DALProfessionAreaType theType = new DALProfessionAreaType();
            dsType = theType.GetList("");
            if (dsType.Tables.Count > 0)
            {
                this.ddlProfessionAreaType.DataSource = dsType.Tables[0].DefaultView;
                this.ddlProfessionAreaType.DataTextField = "Name";
                this.ddlProfessionAreaType.DataValueField = "Id";
                this.ddlProfessionAreaType.DataBind();
            }
           // this.ddlProfessionAreaType.Items.Insert(0, new ListItem("所属专区", ""));


        }

        private void GridViewBind(string Sqlsort)
        {
            string sql = GetSql();
            if (Sqlsort == "")
            {
                sql = sql + " order by 日期 desc ";
            }
            else
            {
                sql = sql + Sqlsort;
            }
            DataTable table = new DataTable();
            table = SQLAccess.GreatDs(sql).Tables[0];
            this.GridView1.DataSource = table.DefaultView;
            this.GridView1.DataBind();
            this.LblCurrentIndex.Text = "当前第 " + ((this.GridView1.PageIndex + 1)).ToString() + "页";
            this.LblPageCount.Text = "共 " + this.GridView1.PageCount.ToString() + "页";
            this.LblRecordCount.Text = "总计 " + table.Rows.Count.ToString() + "个记录";
            if (table.Rows.Count == 0)
            {
                this.btnFirst.Visible = false;
                this.btnPrev.Visible = false;
                this.btnNext.Visible = false;
                this.btnLast.Visible = false;
                this.LblCurrentIndex.Visible = false;
                this.LblPageCount.Visible = false;
                this.LblRecordCount.Visible = false;
            }
            else if (this.GridView1.PageCount == 1)
            {
                this.btnFirst.Visible = true;
                this.btnPrev.Visible = true;
                this.btnNext.Visible = true;
                this.btnLast.Visible = true;
                this.LblCurrentIndex.Visible = true;
                this.LblPageCount.Visible = true;
                this.LblRecordCount.Visible = true;
            }
            this.btnFirst.CommandName = "1";
            this.btnPrev.CommandName = (this.GridView1.PageIndex == 0) ? "1" : this.GridView1.PageIndex.ToString();
            this.btnNext.CommandName = (this.GridView1.PageCount == 1) ? this.GridView1.PageCount.ToString() : ((this.GridView1.PageIndex + 2)).ToString();
            this.btnLast.CommandName = this.GridView1.PageCount.ToString();
            this.ddlCurrentPage.Items.Clear();
            if (this.GridView1.PageCount > 0)
            {
                for (int i = 1; i <= this.GridView1.PageCount; i++)
                {
                    this.ddlCurrentPage.Items.Add(i.ToString());
                }
                this.ddlCurrentPage.SelectedIndex = this.GridView1.PageIndex;
            }
        }






        protected void Page_Load(object sender, EventArgs e)
        {

            if (!base.IsPostBack)
            {
               this.BindProfessionAreaType();
                base.ValidateLoginRule(ERule.OrderStat);
                this.BindYearAndMonth();
               // this.GridViewBind("");

            }
        }

        protected void PagerButtonClick(object sender, EventArgs e)
        {
            this.GridView1.PageIndex = Convert.ToInt32(((LinkButton)sender).CommandName) - 1;
            this.GridViewBind("");
        }

        protected void ibtnSearch_Click(object sender, ImageClickEventArgs e)
        {
            this.GridViewBind("");
        }

        protected void DropDownList1_SelectedIndexChanged1(object sender, EventArgs e)
        {
            this.GridViewBind("");
        }

        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            if (ViewState["Ascending"] == null)
            {
                ViewState["Ascending"] = " desc ";
                this.GridViewBind(" order by " + e.SortExpression + " asc ");
            }
            else
            {
                this.GridViewBind(" order by " + e.SortExpression + ViewState["Ascending"].ToString());
                if (ViewState["Ascending"].ToString() == " asc ")
                {
                    ViewState["Ascending"] = " desc ";
                }
                else
                {
                    ViewState["Ascending"] = " asc ";
                }
            }
           
        }

        protected void ibtnExport_Click(object sender, ImageClickEventArgs e)
        {
            string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".csv";//设置导出文件的名称   
            string sql = GetSql();
            
                sql = sql + " order by 日期 desc ";
           
            DataTable table = new DataTable();
            table = SQLAccess.GreatDs(sql).Tables[0];
            string sTitle = string.Empty;
            if (!string.IsNullOrEmpty(this.ddlMonth.SelectedItem.Value))
            {
                sTitle = " 订单数量统计表(" + this.ddlYear.SelectedItem.Value + "年" + this.ddlMonth.SelectedItem.Value + "月)";
            }
            else
            {
                sTitle = " 订单数量统计表(" + this.ddlYear.SelectedItem.Value + "年)";
            }
            Gift.Utile.OperateExcel.ExportCSVWithTitle(table, fileName, sTitle);
        }

        void BindYearAndMonth()
        {
            for (int i = 2011; i <= 2015; i++)
            {
                this.ddlYear.Items.Add(i.ToString());
            }

            for (int i = 1; i <= 12; i++)
            {
                this.ddlMonth.Items.Add(i.ToString());
            }
            this.ddlMonth.Items.Insert(0, new ListItem("请选取月", ""));
        }


    }
}